package com.ncloud.crm.mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.*;

import com.ncloud.crm.entity.order.Order;



@Mapper
public interface OrderMapper {
  
    @Select(value = { "select * from bs_o_order where order_id = #{orderId}" })
	@Results({ @Result(column = "order_id", property = "orderId"), @Result(column = "cust_id", property = "custId"),
			@Result(column = "order_type", property = "orderType"),
			@Result(column = "order_state", property = "orderState"),
			@Result(column = "exec_time", property = "execTime"),
			@Result(column = "create_date", property = "createDate"),
			@Result(column = "finish_time", property = "finishTime"),
			@Result(column = "channel_id", property = "channelId"),
			@Result(column = "cust_name", property = "custName"),
			@Result(column = "prod_id", property = "prodId"),
			@Result(column = "create_user", property = "createUser"), @Result(column = "remark", property = "remark") })
    public Order getOrderPk(Long orderId);
    
    @Select(value = { "select * from bs_oh_order where order_id = #{orderId}" })
	@Results({ @Result(column = "order_id", property = "orderId"), @Result(column = "cust_id", property = "custId"),
			@Result(column = "order_type", property = "orderType"),
			@Result(column = "order_state", property = "orderState"),
			@Result(column = "exec_time", property = "execTime"),
			@Result(column = "create_date", property = "createDate"),
			@Result(column = "finish_time", property = "finishTime"),
			@Result(column = "channel_id", property = "channelId"),
			@Result(column = "cust_name", property = "custName"),
			@Result(column = "prod_id", property = "prodId"),
			@Result(column = "create_user", property = "createUser"), @Result(column = "remark", property = "remark") })
    public Order getHisOrderPk(Long orderId);
  

    @Insert(value = "insert into bs_o_order(order_id,cust_id,cust_name,order_type,order_state,create_date,exec_time,prod_id," +
            "finish_time,channel_id,create_user,remark) values(#{orderId},#{custId},#{custName},#{orderType},#{orderState}," +
            "#{createDate},#{execTime},#{prodId},#{finishTime},#{channelId},#{createUser},#{remark})")
    public int insertOrder(Order order);

    @Select(value = { "select b.*,c.fee from (select a.order_id,a.cust_id,a.cust_name,a.prod_id,f_get_prodName(a.prod_id)"
    		+ " as prod_name,a.order_type,a.order_state, f_get_label('ORDER_STATE',a.order_state) as order_state_name,f_date_format"
    		+ "(a.create_date) as create_date,a.exec_time,a.finish_time,a.channel_id, a.create_user,a.remark,"
    		+ "(select count(*) from bs_oh_work where order_id =a.order_id) as node_count from bs_oh_order a where"
    		+ " a.create_user = #{userId}  and if(#{orderState}=0,a.order_state != '0',a.order_state = #{orderState}) and a.cust_name like concat('%',#{custName},'%') "
    		+ "union  select a.order_id,a.cust_id,a.cust_name,a.prod_id,f_get_prodName(a.prod_id) as prod_name,"
    		+ "a.order_type,a.order_state, f_get_label('ORDER_STATE',a.order_state) as order_state_name,f_date_format(a.create_date)"
    		+ " as create_date,a.exec_time,f_date_format(a.finish_time) as finish_time,a.channel_id, a.create_user"
    		+ ",a.remark,(select count(*) from bs_o_work where order_id =a.order_id) as node_count from "
    		+ "bs_o_order a where a.create_user = #{userId} and if(#{orderState}=0,a.order_state != '0',a.order_state = #{orderState}) and "
    		+ "a.cust_name like concat('%',#{custName},'%') )b ,(select order_id,round(sum(received_fee)/100,2) as fee from "
    		+ "bs_o_discnt_fee group by order_id  ) c where b.order_id=c.order_id  order by create_date desc" })
    @Results({ @Result(javaType=String.class, column = "order_id", property = "orderId"), @Result(javaType=String.class, column = "cust_id", property = "custId"),
   	 @Result(column = "cust_name", property = "custName"),@Result(column = "order_type", property = "orderType"),
       @Result(column="order_state",property="orderState"),@Result(column="exec_time",property="execTime"),
       @Result(column="order_state_name",property="orderStateName"),
       @Result(column="create_date",property="createDate"),@Result(column="finish_time",property="finishTime"),
       @Result(javaType=String.class, column="channel_id",property="channelId"),@Result(javaType=String.class, column="create_user",property="createUser"),
       @Result(column="remark",property="remark"),@Result(javaType=String.class, column = "prod_id", property = "prodId"),
       @Result(column = "prod_name", property = "prodName"),@Result(column = "fee", property = "fee"),@Result(column = "node_count", property = "nodeCount")})
	public List<Map<Object, Object>> getMyOrderListByCreateUser(@Param("userId")Long userId,@Param("custName")String custName, @Param("orderState")String orderState );
    
    @Select(value = { "select a.order_id,a.cust_id,a.cust_name,a.prod_id,f_get_prodName(a.prod_id) as prod_name,a.order_type,"
    		+ " a.order_state,f_get_label('ORDER_STATE',a.order_state) as order_state_name,f_date_format(a.create_date) as create_date,a.exec_time,f_date_format(a.finish_time) as finish_time,a.channel_id,"
    		+ " a.create_user,a.remark,(select count(*) from bs_o_work where order_id =a.order_id) as node_count"
    		+ " from bs_o_order a where a.create_user = #{userId}" })
    @Results({ @Result(javaType=String.class, column = "order_id", property = "orderId"), @Result(javaType=String.class, column = "cust_id", property = "custId"),
    	 @Result(column = "cust_name", property = "custName"),@Result(column = "order_type", property = "orderType"),
        @Result(column="order_state",property="orderState"),@Result(column="exec_time",property="execTime"),
        @Result(column="order_state_name",property="orderStateName"),
        @Result(column="create_date",property="createDate"),@Result(column="finish_time",property="finishTime"),
        @Result(javaType=String.class, column="channel_id",property="channelId"),@Result(column="create_user",property="createUser"),
        @Result(column="remark",property="remark"),@Result(javaType=String.class, column = "prod_id", property = "prodId"),
        @Result(column = "prod_name", property = "prodName"),@Result(column = "node_count", property = "nodeCount")})
	public List<Map<String, Object>> getOrderListByCreateUser(Long userId);
    
    @Select(value = { "select a.order_id,a.cust_id,a.cust_name,a.prod_id,f_get_prodName(a.prod_id) as prod_name,a.order_type,order_state,"
    		+ " f_get_label('ORDER_STATE',a.order_state) as order_state_name,f_date_format(a.create_date) as create_date,a.exec_time,a.finish_time,a.channel_id,"
    		+ " a.create_user,a.remark,(select count(*) from bs_oh_work where order_id =a.order_id) as node_count"
    		+ " from bs_oh_order a where a.create_user = #{userId}" })
    @Results({ @Result(javaType=String.class, column = "order_id", property = "orderId"), @Result(javaType=String.class, column = "cust_id", property = "custId"),
    		@Result(column = "cust_name", property = "custName"),@Result(column = "order_type", property = "orderType"),
    		@Result(column="order_state",property="orderState"),@Result(column="exec_time",property="execTime"),
    		@Result(column="create_date",property="createDate"),@Result(column="finish_time",property="finishTime"),
    		@Result(javaType=String.class, column="channel_id",property="channelId"),@Result(javaType=String.class, column="create_user",property="createUser"),
    		@Result(column="remark",property="remark"),@Result(javaType=String.class, column = "prod_id", property = "prodId"),
    		@Result(column = "prod_name", property = "prodName"),@Result(column = "node_count", property = "nodeCount"),
    		@Result(column="order_state_name",property="orderStateName")})
	public List<Map<String, Object>> getHisOrderListByCreateUser(Long userId);
   
    @Select(value = { "select a.order_id,a.cust_id,a.cust_name,a.prod_id,f_get_prodName(a.prod_id) as prod_name,"
    		+ " if(a.order_type=10,'新增','变更') as order_type,order_state, f_get_label('ORDER_STATE',a.order_state) as order_state_name,"
    		+ " f_date_format(a.create_date) as create_date,a.exec_time,f_date_format(a.finish_time) as finish_time,b.chnl_id,"
    		+ " f_get_chnlName(b.chnl_id) as chnl_name, a.create_user,a.remark,"
    		+ " (select count(*) from bs_o_node where order_id =a.order_id) as node_count"
    		+ " from (select * from bs_o_order where cust_name like concat('%',#{custName},'%')"
    		+ " and (order_state = #{orderState} or #{orderState}='0')"
    		+ " union select * from bs_oh_order where cust_name like concat('%',#{custName},'%')"
    		+ " and (order_state = #{orderState} or #{orderState}='0')) a,sys_sales b where a.create_user = b.user_id "
    		//+ " and (FIND_IN_SET(b.chnl_id,f_getChildChnl(#{chnlId})) or #{chnlId} is null) order by a.create_date desc " })
    		+ " and (b.chnl_id=#{chnlId} or #{chnlId} is null) order by a.create_date desc " })
    @Results({ @Result(javaType=String.class, column = "order_id", property = "orderId"), @Result(javaType=String.class, column = "cust_id", property = "custId"),
   	 @Result(column = "cust_name", property = "custName"),@Result(column = "prod_id", property = "prodId"),
       @Result(column="prod_name",property="prodName"),@Result(column="order_type",property="orderType"),
       @Result(column="order_state",property="orderState"),@Result(column="create_date",property="createDate"),
       @Result(column="exec_time",property="execTime"),@Result(column="finish_time",property="finishTime"),
       @Result(javaType=String.class, column="chnl_id",property="chnl_id"),@Result(column="create_user",property="createUser"),
       @Result(column="remark",property="remark"),@Result(javaType=String.class, column = "chnl_name", property = "chnlName"),
       @Result(column = "node_count", property = "nodeCount"),@Result(column="order_state_name",property="orderStateName")})
	public List<Map<String, Object>> getAllOrderList(@Param("chnlId")Long chnlId, @Param("custName")String custName, @Param("orderState")String orderState);    
       

    @Update("update bs_o_order set order_state=#{orderState},finish_time=ifnull(#{finishTime},finish_time) where order_id=#{orderId}")
    int updateOrderState(Order order);

    /*查询当前不需要施工的订单信息*/
    @Select(value = { "select a.order_id from bs_o_order a where not exists (select 1 from bs_o_work b where b.order_id =a.order_id) and a.order_id =#{0}" })
    @Results({ @Result(column = "order_id", property = "orderId")})
    public Long getOrderNoWorkInfo(Long orderId);

    @Insert(value = "insert into bs_oh_order(order_id,cust_id,cust_name,order_type,order_state,create_date,exec_time,prod_id," +
			"finish_time,channel_id,create_user,remark)" +
            " select order_id,cust_id,cust_name,order_type,order_state,create_date,exec_time,prod_id," +
			"finish_time,channel_id,create_user,remark from bs_o_order where order_id = #{order_id}")
	public int insertHisOrder(Long orderId);

    @Delete("delete from bs_o_order where order_id =#{orderId}")
	public int deleteByOrderId(Long orderId);

    @Select(value = { "select a.order_id,a.cust_id,a.cust_name,a.prod_id,f_get_prodName(a.prod_id) as prod_name,a.order_type,"
    		+ " f_get_label('ORDER_STATE',a.order_state) as order_state,f_date_format(a.create_date) as create_date,a.exec_time,f_date_format(a.finish_time) as finish_time,a.channel_id,"
    		+ " a.create_user,a.remark,(select count(*) from bs_o_work where order_id =a.order_id) as node_count"
    		+ " from bs_o_order a where EXISTS (SELECT 1 FROM bs_o_work WHERE order_id = a.order_id)" })
    @Results({ @Result(javaType=String.class, column = "order_id", property = "orderId"), @Result(javaType=String.class, column = "cust_id", property = "custId"),
    	 @Result(column = "cust_name", property = "custName"),@Result(column = "order_type", property = "orderType"),
        @Result(column="order_state",property="orderState"),@Result(column="exec_time",property="execTime"),
        @Result(column="create_date",property="createDate"),@Result(column="finish_time",property="finishTime"),
        @Result(javaType=String.class, column="channel_id",property="channelId"),@Result(column="create_user",property="createUser"),
        @Result(column="remark",property="remark"),@Result(javaType=String.class, column = "prod_id", property = "prodId"),
        @Result(column = "prod_name", property = "prodName"),@Result(column = "node_count", property = "nodeCount")})
	public List<Map<String, Object>> getNeedConstructOrders();

} 